package accesoADatos;


import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;

import datos.Caja;
import datos.Cliente;
import datos.Compra;
import datos.Producto;
import datos.Profesional;
import datos.ProfesionalRealizaServicio;
import datos.Proveedor;
import datos.Reserva;
import datos.Retiro;
import datos.Servicio;
import datos.Turno;
import datos.Venta;

public class AccesoADatos {
	
	private Configuration configuration=null;
	private SessionFactory sessionFactory=null;
	private Session session=null;

	private static AccesoADatos instance=new AccesoADatos();
	
	public static AccesoADatos getInstance() {
		return instance;
	}
	public Configuration getConfiguration() {

	if (configuration == null) {
		configuration = new Configuration();
	try {
		
		configuration.addClass(datos.Proveedor.class);
		configuration.addClass(datos.Profesional.class);
		configuration.addClass(datos.Producto.class);
		configuration.addClass(datos.Caja.class);
		configuration.addClass(datos.Cliente.class);
		configuration.addClass(datos.Servicio.class);
		configuration.addClass(datos.ProfesionalRealizaServicio.class);
		configuration.addClass(datos.Turno.class);
		configuration.addClass(datos.Reserva.class);
		configuration.addClass(datos.Venta.class);
		configuration.addClass(datos.Retiro.class);
		configuration.addClass(datos.Compra.class);
		
		
	} catch (MappingException e) {
	}

	}
	return configuration;

	}
	public SessionFactory getSessionFactory() {
		if (sessionFactory == null) {
			try {
				sessionFactory = getConfiguration().buildSessionFactory();
			} 
			catch (HibernateException e) {
				
			}

	}

	return sessionFactory;

	}
	public Session getSession() {
		if (session == null || !session.isOpen()) {
			try {
				session = getSessionFactory().openSession();
			} catch (HibernateException e) {
				e.printStackTrace();
				}
		}
		return session;
	}
	
	@SuppressWarnings("unchecked")
	public List getServiciosRangoXFechaXProfesional(Date inicio, Date  fin, Integer idProfesional){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM ProfesionalRelizaServicio PRS WHERE PRS.fecha >= '"+inicio+"' AND PRS.fecha <= '"+fin+"' "+" AND " +
					"PRS.profesional = '"+idProfesional +"'";
			Query q= session.createQuery(consulta);
			l=q.list();
			
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}

		}
		return l;
			
	}
	@SuppressWarnings("unchecked")
	public List getServiciosXRangoFecha(Date inicio, Date  fin){
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM ProfesionalRealizaServicio WHERE fecha >= '"+inic+"' AND "+" fecha <= '"+f+"'";
			Query q= session.createQuery(consulta);
			l=q.list();			

		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
			try {
				
				} catch (HibernateException e) {

				}
		}
		return l;	
	}
	
	public void setObjeto(Object o) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(o);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}

		}
		
	}
	

	
	public void BorrarCliente(Cliente cliente) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(cliente);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarCaja(Caja caja) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(caja);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarProducto(Producto producto) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(producto);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarProfesional(Profesional prof) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(prof);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarProveedor(Proveedor prov) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(prov);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarReserva(Reserva res) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(res);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarServicio(Servicio serv) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(serv);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarTurno(Turno turno) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(turno);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarVenta(Venta venta) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(venta);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	public void BorrarProfesionalRealizaServicio(ProfesionalRealizaServicio prof) {
		Session session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.delete(prof);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		}
	
	@SuppressWarnings("unchecked")
	public List getTurnosRangoFecha(Date inicio, Date fin){
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Reserva R WHERE R.turno.fecha >= '"+inic+"' AND "+" R.turno.fecha <= '"+f+"' "+
					"Order by R.profesional.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
			try {
			} catch (HibernateException e) {
			}
			}
		return l;
	}
	
	public List getTurnosActivosRangoFecha(Date inicio, Date fin){
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Reserva R WHERE R.turno.fecha >= '"+inic+"' AND "+" R.turno.fecha <= '"+f+"' "+
					"AND R.profesional.estado = 'Activo' Order by R.profesional.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
			try {
			} catch (HibernateException e) {
			}
			}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getProfesionales(){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Profesional P where P.estado = 'Activo'";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {

		} catch (HibernateException e) {
		}
		}
		return l;
	}
	@SuppressWarnings("unchecked")
	public List getProfesionalesTodos(){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Profesional P Order by P.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {

		} catch (HibernateException e) {
		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getServicios(){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Servicio S Order by S.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		return l;
	}

	@SuppressWarnings("unchecked")
	public List getServicio(Integer id){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Servicio S Order by S.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getClientes() {
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Cliente S Order by S.apellido, S.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {

		} catch (HibernateException e) {

		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public Caja getCaja() {
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Caja C WHERE C.saldoEfectivo = " + null;
			Query q = session.createQuery(consulta);
			l = q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(l.size()!= 0)
			return (Caja)l.get(0);
		return null;
	}
	
	public Double getTotalFacturado(Date fecha_inicio, Date fecha_fin){
		session=this.getSession();
		Double d = new Double(0);
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (P.importe) FROM ProfesionalRealizaServicio P Where P.fecha >= '" + fecha_inicio + "' AND P.fecha <= '" + fecha_fin + "'";
			d = (Double)session.createQuery(consulta).uniqueResult();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(d==null)
			return (new Double(0));
		return d;
	}
	
	@SuppressWarnings("unchecked")
	public List getProveedores() {
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Proveedor P Order by P.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getCajas(Date inicio, Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Caja C WHERE C.fecha >= '"+inic+"' AND C.fecha <= '"+f+"' Order by C.fecha";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {

		} catch (HibernateException e) {
		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getProductos() {
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Producto P Order by P.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {

		try {
		
		} catch (HibernateException e) {
		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getProductos(Integer idProveedor) {
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Producto P WHERE P.proveedor = idProveedor Order by P.nombre";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
			
		} catch (HibernateException e) {
		}
		}
		return l;
	}
	

	public Double getTotalRetiros(Caja c) {
		session=this.getSession();
		Double d = null;
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (R.importe) FROM Retiro R WHERE R.caja.idCaja = '" + c.getIdCaja() + "'";
			d = (Double) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {

		} catch (HibernateException e) {
		}
		}
		if(d == null)
			return (new Double(0));
		return d;
	}

	public double getTotalCorte(Profesional p, Date inicio, Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		Double d = null;
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (PRS.importe) FROM ProfesionalRealizaServicio PRS WHERE PRS.profesional.idProfesional = '" + p.getIdProfesional() + "' AND"
			+" PRS.fecha >= '"+inic+"' AND "+" PRS.fecha <= '"+f+"' " +"and PRS.servicio.tipo = 'Corte'";
			
			d = (Double) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(d == null)
			return (new Double(0));
		return d;
	}
	
	public double getTotalColor(Profesional p, Date inicio, Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		Double d = null;
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (PRS.importe) FROM ProfesionalRealizaServicio PRS WHERE PRS.profesional.idProfesional = '" + p.getIdProfesional() + "' AND"
			+" PRS.fecha >= '"+inic+"' AND "+" PRS.fecha <= '"+f+"' " +"and PRS.servicio.tipo = 'Color'";
			
			d = (Double) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(d == null)
			return (new Double(0));
		return d;
	}
	
	public Producto getProducto(String codigo){
		session=this.getSession();
		Producto p = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Producto P WHERE P.codigoBarra = '"+ codigo +"' Order by P.nombre";
			p = (Producto) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
			
		} catch (HibernateException e) {
		}
		}
		return p;
	}
	public Reserva getClientesReserva(String col, Turno t) {
		SimpleDateFormat fechas = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dia=fechas.format(t.getFecha().getTime());
		session=this.getSession();
		Reserva p = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Reserva R WHERE R.profesional.nombre= '"+col+"' AND R.turno.fecha ='"+dia+"'";
			p = (Reserva) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
			//this.session.close();
		try {
			
		} catch (HibernateException e) {
		}
		}
		return p;
	}
	public int getUltimaCompra() {
		session=this.getSession();
		Integer i = null;
		try {
			session.beginTransaction();
			String consulta = "SELECT max (C.id.idCompra) FROM Compra C";			
			i = (Integer) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(i == null)
			return (new Integer(0));
		return i;
	}
	public void setCompra(Compra c) {
		session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}

		}
		
	}
	public void setVenta1(Venta v) {
	session = getSession();
	Transaction tx = null;

	try {
		tx = session.beginTransaction();
		session.saveOrUpdate(v);
		tx.commit();

	} catch (HibernateException e) {
		System.out.print(e);
		if (tx != null) {
			try {
				tx.rollback();
	} catch (HibernateException e1) {
	}
	}
	}
	finally {
	try {
	session.close();
	} catch (HibernateException e) {

	}
	}
}
	public int getUltimaVenta() {
		session=this.getSession();
		Integer i = null;
		try {
			session.beginTransaction();
			String consulta = "SELECT max (C.id.idVenta) FROM Venta C";			
			i = (Integer) session.createQuery(consulta).uniqueResult();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(i == null)
			return (new Integer(0));
		return i;
	}
	@SuppressWarnings("unchecked")
	public List getTurnosExtrasProf(Profesional p, Date date) {
		java.sql.Date d1=new java.sql.Date(date.getTime());
		long time = (24*60*60*1000)+d1.getTime();
		java.sql.Date d2=new java.sql.Date(time);
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			//String consulta = "FROM Reserva R where R.profesional.idProfesional = '"+p.getIdProfesional()+"' and R.turno.fecha='"+fecha+"' and R.tipo = 'Extra' Order by R.turno.fecha";
			//String consulta = "FROM Reserva R where R.turno.fecha='"+fecha+"' and R.tipo = 'Extra' Order by R.turno.fecha";
			String consulta = "FROM Reserva R where R.profesional = '"+p+"' and R.turno.fecha >'"+d1+"' and R.turno.fecha <'"+d2+"'and R.tipo = 'Extra' Order by R.turno.fecha";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally { 
		try {

		} catch (HibernateException e) {
		}
		}
		return l;
	}
	
	@SuppressWarnings("unchecked")
	public List getReservasExtras(Profesional p, Date d){
		java.sql.Date dia = new java.sql.Date(d.getTime()-1);
		java.sql.Date sig = new java.sql.Date(d.getTime()+24*60*60*1000+1);
		session=this.getSession();
		List l = null;
		try 
		{
		session.beginTransaction();
		String consulta = "FROM Reserva R WHERE R.profesional = '"+p.getIdProfesional()+"' AND R.tipo = 'EXTRA' AND R.turno.fecha >= '"+dia+"' AND R.turno.fecha < '"+sig+"'";
		Query q = session.createQuery(consulta);
		l = q.list();
		} catch (HibernateException e) {
		System.out.print(e);
		}
		finally {
		try {
		} catch (HibernateException e) {
		}
		}
		return l;
		}
	
	public List getHorarios(Profesional p){
		session=this.getSession();
		List l = null;
		try 
		{
		session.beginTransaction();
		String consulta = "FROM Horario R WHERE R.profesional = '"+p.getIdProfesional()+"' Order by R.profesional"; 
		Query q = session.createQuery(consulta);
		l = q.list();
		} catch (HibernateException e) {
		System.out.print(e);
		}
		finally {
		try {
		} catch (HibernateException e) {
		}
		}
		return l;
		}
	public void setCompra(Venta v) {
		session = getSession();
		Transaction tx = null;

		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(v);
			tx.commit();

		} catch (HibernateException e) {
			System.out.print(e);
			if (tx != null) {
				try {
					tx.rollback();
		} catch (HibernateException e1) {
		}
		}
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}

		}
		
	}
	public List getCompras(Date inicio,Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try 
		{
		session.beginTransaction();
		String consulta = "FROM Compra C where C.fecha >= '"+inic+"' AND "+" C.fecha <= '"+f+"'";
		Query q = session.createQuery(consulta);
		l = q.list();
		} catch (HibernateException e) {
		System.out.print(e);
		}
		finally {
		try {
		} catch (HibernateException e) {
		}
		}
		return l;
		}
	public List getVentas(Date inicio, Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try 
		{
		session.beginTransaction();
		String consulta = "FROM Venta C where C.fecha >= '"+inic+"' AND "+" C.fecha <= '"+f+"'";
		Query q = session.createQuery(consulta);
		l = q.list();
		} catch (HibernateException e) {
		System.out.print(e);
		}
		finally {
		try {
		} catch (HibernateException e) {
		}
		}
		return l;
	}
	public List getRetiros(Date inicio, Date fin) {
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		List l = null;
		try 
		{
		session.beginTransaction();
		String consulta = "FROM Retiro C where C.fecha >= '"+inic+"' AND "+" C.fecha <= '"+f+"' Order by C.fecha";
		Query q = session.createQuery(consulta);
		l = q.list();
		} catch (HibernateException e) {
		System.out.print(e);
		}
		finally {
		try {
		} catch (HibernateException e) {
		}
		}
		return l;
	}
	public Object getProveedor(Integer idProducto){
		session=this.getSession();
		String i = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Producto P where P.idProducto= '"+idProducto+"'";			
			i = ((Producto) session.createQuery(consulta).uniqueResult()).getProveedor().toString();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(i == null)
			return (new Integer(""));
		return i;
	}
	public Double getTotalVentas(Date fecha, Date date) {
		session=this.getSession();
		Double d = new Double(0);
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (V.total) FROM Venta V Where V.fecha >= '" + fecha + "' AND V.fecha <= '" + date + "'";
			d = (Double)session.createQuery(consulta).uniqueResult();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(d==null)
			return (new Double(0));
		return d;
	}
	public Double getTotalCompras(Caja c, Date date) {
		session=this.getSession();
		Double d = new Double(0);
		try {
			session.beginTransaction();
			String consulta = "SELECT SUM (C.importe) FROM Compra C Where C.fecha >= '" + c.getFecha()+ "' AND C.fecha <= '" + date + "'";
			d = (Double)session.createQuery(consulta).uniqueResult();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if(d==null)
			return (new Double(0));
		return d;
	}
	public void BorrarCajasMesAnterior() {
		int mesActual=Calendar.getInstance().getTime().getMonth()+1;
		int mesAnterior;
		if (mesActual==1)
			mesAnterior=12;
		else
			mesAnterior=mesActual-1;
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM Caja C WHERE Month(C.fecha) = '"+ mesAnterior+"'";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
			
		} catch (HibernateException e) {
		}
		}
		
		for (int i=0; i<l.size(); i++){
			this.BorrarCaja((Caja)l.get(i));
		}

		}
	public void BorrarServicios() {
		int mesActual=Calendar.getInstance().getTime().getMonth()+1;
		int mesAnterior;
		if (mesActual==1)
			mesAnterior=12;
		mesAnterior=mesActual-1;
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM ProfesionalRealizaServicio PRS WHERE Month(PRS.fecha) = '"+ mesAnterior+"'";
			Query q= session.createQuery(consulta);
			l=q.list();
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
			
		} catch (HibernateException e) {
		}
		}
		
		for (int i=0; i<l.size(); i++){
			this.BorrarProfesionalRealizaServicio((ProfesionalRealizaServicio)l.get(i));
		}

		
	}
	
	public int getCantidadServicios(Integer idProfesional, Servicio s, Date inicio, Date fin){
		java.sql.Date inic=new java.sql.Date(inicio.getTime());
		java.sql.Date f=new java.sql.Date(fin.getTime()+24*60*60*1000);
		session=this.getSession();
		Long d = new Long(0);
		try {
			session.beginTransaction();
			/*String consulta = "SELECT COUNT (PRS) FROM ProfesionalRealizaServicio PRS Where PRS.fecha >= '" + inicio+ "' AND C.fecha <= '" + fin + "'";*/
			
			String consulta = "SELECT COUNT (*) FROM ProfesionalRealizaServicio PRS WHERE PRS.fecha >= '"+inic+"' AND "+" PRS.fecha <= '"+f+"' "+" AND " +
			"PRS.profesional.idProfesional = '"+idProfesional +"' AND PRS.servicio.idServicio = '"+s.getIdServicio()+"'";
			d = (Long)session.createQuery(consulta).uniqueResult();
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {
		}
		}
		if (d == null)
			return new Integer(0);
		else
			return d.intValue();
	}
	
	
	@SuppressWarnings("unchecked")
	public List getHistorialCliente(Cliente c){
		session=this.getSession();
		List l = null;
		try {
			session.beginTransaction();
			String consulta = "FROM ProfesionalRealizaServicio PRS WHERE PRS.cliente.idCliente = '"+c.getIdCliente()+"'";
			Query q= session.createQuery(consulta);
			l=q.list();
			
			
		} catch (HibernateException e) {
			System.out.print(e);
		}
		finally {
		try {
		session.close();
		} catch (HibernateException e) {

		}

		}
		return l;
			
	}
	
}
